﻿Imports System.Data.SqlClient

Public Class 入账查询

    ' 数据库连接字符串
    Private connectionString As String = DbConnectionHelper.GetConnectString

    ' 窗体加载时默认日期范围为本月
    Private Sub 入账查询_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dtpStart.Value = New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)
        dtpEnd.Value = DateTime.Now
        cboRZFS.SelectedIndex = 0 ' 默认选中“全部”

        LoadData()
    End Sub

    ' 点击“查询”按钮执行查询逻辑
    Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
        LoadData()
    End Sub

    ' 加载数据到 DataGridView
    Private Sub LoadData()
        Try
            Using conn As New SqlConnection(connectionString)
                conn.Open()

                ' 构建 SQL 查询语句
                Dim sql As String = "SELECT ID, RZJE, RZSJ, MEMO, DD, " &
                                    "CASE RZFS WHEN 1 THEN '现金' WHEN 2 THEN '转账' WHEN 3 THEN '刷卡' ELSE '其他' END AS 入账方式, ZFR " &
                                    "FROM RZJLK WHERE RZSJ BETWEEN @ks AND @js"

                ' 如果选择了具体入账方式
                If cboRZFS.SelectedIndex > 0 Then
                    sql &= " AND RZFS = @rzfs"
                End If

                sql &= " ORDER BY RZSJ DESC"

                Dim cmd As New SqlCommand(sql, conn)
                cmd.Parameters.AddWithValue("@ks", dtpStart.Value)
                cmd.Parameters.AddWithValue("@js", dtpEnd.Value)

                If cboRZFS.SelectedIndex > 0 Then
                    cmd.Parameters.AddWithValue("@rzfs", cboRZFS.SelectedIndex)
                End If

                Dim adapter As New SqlDataAdapter(cmd)
                Dim dt As New DataTable()
                adapter.Fill(dt)

                dgvData.DataSource = dt

                ' 计算总金额
                Dim total As Double = 0
                For Each row As DataRow In dt.Rows
                    If Not IsDBNull(row("RZJE")) Then
                        total += Convert.ToDouble(row("RZJE"))
                    End If
                Next

                lblTotal.Text = "总入账金额：" & total.ToString("C2") ' 格式为 ￥x.xx
            End Using

        Catch ex As Exception
            MessageBox.Show("查询失败：" & ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

End Class
